package database;

import java.awt.Color;
import java.awt.Shape;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;

import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartFrame;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.plot.XYPlot;
import org.jfree.chart.renderer.xy.XYErrorRenderer;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.statistics.HistogramDataset;
import org.jfree.data.statistics.HistogramType;
import org.jfree.data.xy.YIntervalSeries;
import org.jfree.data.xy.YIntervalSeriesCollection;
import org.jfree.util.ShapeUtilities;

public class IODB {
	
	static final String usernameDB = "simulazioneUser";
	static final  String passwordDB = "simulazione123";
	static final  String DBName = "simulazione";
	
	static final  String urlDB = "jdbc:mysql://localhost/"+DBName+"?user="+usernameDB+"&password="+passwordDB;
	
	public static void visualizzaEsperimenti() {
		
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			String query = "SELECT * FROM esperimento";
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			System.out.println("Esperimenti:");
			while (resultSet.next()) {
				System.out.println("Id: " + resultSet.getInt("id") + " - Nome: " + resultSet.getString("nome"));				
			}
			resultSet.close();
			statement.close();
			connect.close();
			
			
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
	}
	
	public static void visualizzaCaratteristiche(int idEsperimento) {
		
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			String query = "SELECT * FROM caratteristica WHERE id_esperimento=" + idEsperimento;
			//System.out.println(query);
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			System.out.println("Caratteristiche calcolate nell'esperimento "+idEsperimento+":");
			while (resultSet.next()) {
				System.out.println("Id: " + resultSet.getInt("id") + " - Nome: " + resultSet.getString("nome"));				
			}
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
	}
	
	public static void visualizzaDati(int idCaratteristica) {
		
		try {
			
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			String query = "SELECT * FROM dati WHERE id_caratteristica=" + idCaratteristica;
			//System.out.println(query);
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			System.out.println("Dati (lower interval - center - upper interval:");
			while (resultSet.next()) {
				System.out.println(resultSet.getDouble("lower_interval") + " - " + resultSet.getDouble("center") + " - " + resultSet.getDouble("upper_interval"));				
			}
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
	}
	
	public static void visualizzaGrafico(int idCaratteristica, double valoreTeorico) {
	
		String nomeCaratteristica = "";
		String nomeEsperimento = "";
		int idEsperimento;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			
			String query = "SELECT nome,id_esperimento FROM caratteristica WHERE id = " + idCaratteristica;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeCaratteristica  = resultSet.getString("nome");
			idEsperimento  = resultSet.getInt("id_esperimento");
			query = "SELECT nome FROM esperimento WHERE id = " + idEsperimento;
			resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeEsperimento  = resultSet.getString("nome");
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
		JFreeChart chart = creaGraficoValidazione(idCaratteristica, valoreTeorico, nomeEsperimento, nomeCaratteristica);
		
        ChartFrame frame = new ChartFrame("First", chart);
        frame.pack();
        frame.setVisible(true);	
		
	}
	
	public static void esportaGraficoPNG(String nomeGrafico,int idCaratteristica, double valoreTeorico) {
		
		String nomeCaratteristica = "";
		String nomeEsperimento = "";
		int idEsperimento;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			
			String query = "SELECT nome,id_esperimento FROM caratteristica WHERE id = " + idCaratteristica;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeCaratteristica  = resultSet.getString("nome");
			idEsperimento  = resultSet.getInt("id_esperimento");
			query = "SELECT nome FROM esperimento WHERE id = " + idEsperimento;
			resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeEsperimento  = resultSet.getString("nome");
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
		JFreeChart chart = creaGraficoValidazione(idCaratteristica, valoreTeorico, nomeEsperimento, nomeCaratteristica);
		
        try {
			ChartUtilities.saveChartAsPNG(new File("C:\\Users\\fede1587\\eclipseworkspace\\Simulazione\\images\\" + nomeGrafico), chart, 5000, 300);
	    } catch (IOException e) {
	        System.err.println("Problem occurred creating chart.");
	    }
		
	}
	
	
	public static JFreeChart creaGraficoValidazione(int idCaratteristica, double valoreTeorico, String nomeEsperimento, String nomeCaratteristica) {
		
		//XYSeries series = new XYSeries("Asintoto 1");
		//HistogramDataset dataset = new HistogramDataset();
		
		
		YIntervalSeries series1 = new YIntervalSeries("Campioni positivi");
		YIntervalSeries series2 = new YIntervalSeries("Campioni negativi");		
		int contatore = 1;
		
		try {
			//String query = "SELECT id FROM caratteristica WHERE id_esperimento = " + idEsperimento + " AND nome = '" + caratteristica + "' LIMIT 1";
			//statement = connect.createStatement();
			//resultSet = statement.executeQuery(query);
			//resultSet.next();
			//int idCaratteristica = resultSet.getInt("id");
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			//String query = "SELECT * FROM dati WHERE id_caratteristica = " + idCaratteristica + " LIMIT 0,100";
			String query = "SELECT * FROM dati WHERE id_caratteristica = " + idCaratteristica;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			while (resultSet.next()) {
				
				if((valoreTeorico >= resultSet.getDouble("lower_interval")) && (valoreTeorico <= resultSet.getDouble("upper_interval"))) {				
					series1.add(contatore,resultSet.getDouble("center"),resultSet.getDouble("lower_interval"),resultSet.getDouble("upper_interval"));
				} else {
					series2.add(contatore,resultSet.getDouble("center"),resultSet.getDouble("lower_interval"),resultSet.getDouble("upper_interval"));
				}
				contatore++;			
				
			}			
			
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		YIntervalSeriesCollection dataset = new YIntervalSeriesCollection();
		dataset.addSeries(series1);
		dataset.addSeries(series2);
		
		XYErrorRenderer renderer = new XYErrorRenderer();
		NumberAxis xAxis = new NumberAxis("Esperimento");
        NumberAxis yAxis = new NumberAxis(nomeCaratteristica);
        yAxis.setAutoRangeIncludesZero(false);
        renderer.setSeriesLinesVisible(0,false);
        renderer.setSeriesShapesVisible(0,true);
        renderer.setSeriesLinesVisible(1,false);
        renderer.setSeriesShapesVisible(1,true);
		renderer.setSeriesPaint(0, Color.BLACK);
		renderer.setSeriesPaint(1, Color.RED);
        Shape shape = ShapeUtilities.createDiamond(1);
        //Shape shape = new Ellipse2D.Double(3.0, 3.0, 3.0, 3.0);
		renderer.setSeriesShape(0,shape);
		renderer.setSeriesShape(1,shape);       
        
        YIntervalSeries series = new YIntervalSeries("Valore teorico");
        for(int i=1;i<contatore;i++)			series.add(i,valoreTeorico,valoreTeorico,valoreTeorico);	
        dataset.addSeries(series);
        renderer.setSeriesShapesVisible(2,false);
        renderer.setSeriesLinesVisible(2,true);
        renderer.setSeriesPaint(2, Color.BLUE);
        
        XYPlot plot = new XYPlot(dataset, xAxis, yAxis, renderer);
        JFreeChart chart = new JFreeChart(nomeEsperimento, plot);

        
        return chart;
		
	}


	// Prende come parametro l'id di un esperimento e crea i grafici di tutte le caratteristiche
	public static void esportaGraficiEsperimentoPNG(int idEsperimento, HashMap<String,Double> valoriTeorici, String nomeEsperimento) {
				
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			String query = "SELECT * FROM caratteristica WHERE id_esperimento = " + idEsperimento;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			while(resultSet.next()) {
				esportaGraficoPNG(nomeEsperimento + "_" + resultSet.getString("nome") + ".png",resultSet.getInt("id"), valoriTeorici.get(resultSet.getString("nome")));			
			}
			resultSet.close();
			statement.close();
			connect.close();
			
			
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
		
		
	}
	
	public static JFreeChart creaIstogramma(int idCaratteristica, String nomeEsperimento, String nomeCaratteristica) {
		
		//XYSeries series = new XYSeries("Asintoto 1");
		//HistogramDataset dataset = new HistogramDataset();
		
		double[] series;
		int numberOfResults;
		int contatore = 0;
		HistogramDataset dataset = new HistogramDataset();
		//dataset.setType(HistogramType.RELATIVE_FREQUENCY);
		try {
			//String query = "SELECT id FROM caratteristica WHERE id_esperimento = " + idEsperimento + " AND nome = '" + caratteristica + "' LIMIT 1";
			//statement = connect.createStatement();
			//resultSet = statement.executeQuery(query);
			//resultSet.next();
			//int idCaratteristica = resultSet.getInt("id");
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			
			String query = "SELECT count(*) AS numero FROM dati WHERE id_caratteristica = " + idCaratteristica;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			resultSet.next();			
			numberOfResults  = resultSet.getInt("numero");
			series = new double[numberOfResults];
			
			query = "SELECT * FROM dati WHERE id_caratteristica = " + idCaratteristica;
			resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				series[contatore] = resultSet.getDouble("center");
				contatore++;
				
			}			
			dataset.addSeries("Histogram", series, 1000);
	
		    
			
			
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}

		
		JFreeChart chart = ChartFactory.createHistogram( 
	    		nomeEsperimento, 
	    		nomeCaratteristica, 
	    		"numero", 
	            dataset, 
	            PlotOrientation.VERTICAL, 
	            true, 
	            true, 
	            false
	    		);

        
        return chart;
		
	}
	
	public static void visualizzaIstogramma(String nomeGrafico,int idCaratteristica) {
		
		String nomeCaratteristica = "";
		String nomeEsperimento = "";
		int idEsperimento;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			
			String query = "SELECT nome,id_esperimento FROM caratteristica WHERE id = " + idCaratteristica;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeCaratteristica  = resultSet.getString("nome");
			idEsperimento  = resultSet.getInt("id_esperimento");
			query = "SELECT nome FROM esperimento WHERE id = " + idEsperimento;
			resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeEsperimento  = resultSet.getString("nome");
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
		JFreeChart chart = creaIstogramma(idCaratteristica, nomeEsperimento, nomeCaratteristica); 
		
        ChartFrame frame = new ChartFrame(nomeGrafico, chart);
        frame.pack();
        frame.setVisible(true);	
		
	}
	
	public static void esportaIstogrammaPNG(String nomeGrafico,int idCaratteristica) {
		
		String nomeCaratteristica = "";
		String nomeEsperimento = "";
		int idEsperimento;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connect = DriverManager.getConnection(urlDB);
			
			String query = "SELECT nome,id_esperimento FROM caratteristica WHERE id = " + idCaratteristica;
			Statement statement = connect.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeCaratteristica  = resultSet.getString("nome");
			idEsperimento  = resultSet.getInt("id_esperimento");
			query = "SELECT nome FROM esperimento WHERE id = " + idEsperimento;
			resultSet = statement.executeQuery(query);
			resultSet.next();			
			nomeEsperimento  = resultSet.getString("nome");
			resultSet.close();
			statement.close();
			connect.close();
		} catch(Exception e) {
			System.out.println(e.toString());
		}
		
		JFreeChart chart = creaIstogramma(idCaratteristica, nomeEsperimento, nomeCaratteristica);
		
        try {
			ChartUtilities.saveChartAsPNG(new File("C:\\Users\\fede1587\\eclipseworkspace\\Simulazione\\images\\" + nomeGrafico), chart, 500, 300);
	    } catch (IOException e) {
	        System.err.println("Problem occurred creating chart.");
	    }
		
	}

}
